use master
go

create database Student
on
(
	name='Student',
	filename='D:\Sql\Student.mdf',
	size=5MB,
	maxsize=5MB,
	filegrowth=10%
)
log on
(
	name='Student_log',
	filename='D:\Sql\Student_log.ldf',
	size=5MB,
	maxsize=5MB,
	filegrowth=10%
)

use Student
go

create table StuInfo
(
	StuNo char(5) primary key,
	StuName char(5) not null,
	StuAge int,
	StuAddress varchar(30),
	StuSeat int identity(1,1), 
	StuSex int default(0) check(StuSex=0 or StuSex=1)
)
alter table StuInfo alter column StuName char(10) not null

--truncate table StuInfo

insert into StuInfo(StuNo,StuName,StuAge,StuAddress,StuSex)values
('s2501','张秋利',20,'美国硅谷',1),
('s2502','李斯文',18,'湖北武汉',default),
('s2503','马文才',22,'湖南长沙',1),
('s2504','欧阳俊雄',21,'湖北武汉',default),
('s2505','梅超风',20,'湖北武汉',1),
('s2506','陈旋风',19,'美国硅谷',1),
('s2507','陈旋风',19,'美国硅谷',default)

create table StuExam
(
	ExamNo int primary key identity(1,1),
	StuNo char(5) foreign key references StuInfo(StuNo),
	WrittenExam int,
	LabExam int
)

insert into StuExam(StuNo,WrittenExam,LabExam)values
('s2501',50,70),
('s2502',60,65),
('s2503',86,85),
('s2504',40,80),
('s2505',70,90),
('s2506',85,90)



--25.查询每个地方的学生的平均年龄

select AVG(StuAge) from StuInfo

--26.查询男女生的分别的年龄总和

select SUM(StuAge) 年龄总和,StuSex 学生性别 from StuInfo Group by StuSex

--27.查询每个地方的男女生的平均年龄和年龄的总和

select SUM(StuAge),AVG(StuAge),StuAddress,StuSex from StuInfo Group by StuAddress,StuSex

select * from StuInfo
